"""
数据导入路由
"""
from flask import request, jsonify, current_app
from flask_jwt_extended import jwt_required, get_jwt_identity
from werkzeug.utils import secure_filename
import os
import threading
from concurrent.futures import ThreadPoolExecutor, as_completed
from app.blueprints.api import api_bp
from app.models.user import User
from app.services.data_importer import DataImporter
from app.extensions import db


ALLOWED_EXTENSIONS = {'csv', 'xlsx', 'xls'}


def allowed_file(filename):
    """检查文件扩展名"""
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 根据常见mimetype推断扩展名（用于中文文件名被清洗后丢失点号场景）
MIMETYPE_TO_EXTENSION = {
    'text/csv': '.csv',
    'application/csv': '.csv',
    'text/plain': '.csv',  # 某些浏览器会把csv当成text/plain
    'application/vnd.ms-excel': '.xls',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet': '.xlsx',
}


def safe_filename_with_ext(file_storage):
    """
    生成安全文件名，保留/补全扩展名，并加时间戳避免冲突。
    - 优先使用原始文件名的扩展名
    - 若原始扩展名丢失或被清洗掉，则根据mimetype推断
    """
    import time
    import os

    original_name = file_storage.filename or 'upload'
    base, ext = os.path.splitext(original_name)
    ext = ext.lower()

    # secure清洗仅针对基名，避免丢失点号
    safe_base = secure_filename(base) or 'file'

    # 如果扩展名缺失或不在允许列表，尝试用mimetype推断
    if not ext or ext.replace('.', '') not in ALLOWED_EXTENSIONS:
        guessed_ext = MIMETYPE_TO_EXTENSION.get(getattr(file_storage, 'mimetype', '') or '', '')
        # 回退：若仍无法判断，但原始名里包含csv字样，尽量按csv处理
        if not guessed_ext and 'csv' in original_name.lower():
            guessed_ext = '.csv'
        ext = guessed_ext or ext or '.csv'  # 最后兜底.csv

    timestamp = str(int(time.time()))
    return f"{timestamp}_{safe_base}{ext}"


@api_bp.route('/import/students', methods=['POST'])
@jwt_required()
def import_students():
    """导入学生数据"""
    try:
        current_user_id = int(get_jwt_identity())
        user = User.query.get(current_user_id)
        
        # 只有管理员可以导入数据
        if user.role != 'admin':
            return jsonify({
                'success': False,
                'error': 'Permission denied'
            }), 403
        
        # 检查文件
        if 'file' not in request.files:
            return jsonify({
                'success': False,
                'error': 'No file provided'
            }), 400
        
        file = request.files['file']
        
        if file.filename == '':
            return jsonify({
                'success': False,
                'error': 'No file selected'
            }), 400
        
        if not allowed_file(file.filename):
            return jsonify({
                'success': False,
                'error': 'Invalid file type. Only CSV and Excel files are allowed.'
            }), 400
        
        # 保存文件
        filename = safe_filename_with_ext(file)
        upload_folder = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'uploads')
        os.makedirs(upload_folder, exist_ok=True)
        filepath = os.path.join(upload_folder, filename)
        file.save(filepath)
        
        # 导入数据
        importer = DataImporter()
        success = importer.import_students(filepath)
        summary = importer.get_summary()
        
        # 删除临时文件
        try:
            os.remove(filepath)
        except:
            pass
        
        return jsonify({
            'success': success,
            'data': summary
        }), 200 if success else 400
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@api_bp.route('/import/teachers', methods=['POST'])
@jwt_required()
def import_teachers():
    """导入教师数据"""
    try:
        current_user_id = int(get_jwt_identity())
        user = User.query.get(current_user_id)
        
        # 只有管理员可以导入数据
        if user.role != 'admin':
            return jsonify({
                'success': False,
                'error': 'Permission denied'
            }), 403
        
        # 检查文件
        if 'file' not in request.files:
            return jsonify({
                'success': False,
                'error': 'No file provided'
            }), 400
        
        file = request.files['file']
        
        if file.filename == '':
            return jsonify({
                'success': False,
                'error': 'No file selected'
            }), 400
        
        if not allowed_file(file.filename):
            return jsonify({
                'success': False,
                'error': 'Invalid file type. Only CSV and Excel files are allowed.'
            }), 400
        
        # 保存文件
        filename = safe_filename_with_ext(file)
        upload_folder = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'uploads')
        os.makedirs(upload_folder, exist_ok=True)
        filepath = os.path.join(upload_folder, filename)
        file.save(filepath)
        
        # 导入数据
        importer = DataImporter()
        success = importer.import_teachers(filepath)
        summary = importer.get_summary()
        
        # 删除临时文件
        try:
            os.remove(filepath)
        except:
            pass
        
        return jsonify({
            'success': success,
            'data': summary
        }), 200 if success else 400
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@api_bp.route('/import/courses', methods=['POST'])
@jwt_required()
def import_courses():
    """导入课程数据"""
    try:
        current_user_id = int(get_jwt_identity())
        user = User.query.get(current_user_id)
        
        if user.role != 'admin':
            return jsonify({
                'success': False,
                'error': 'Permission denied'
            }), 403
        
        if 'file' not in request.files:
            return jsonify({
                'success': False,
                'error': 'No file provided'
            }), 400
        
        file = request.files['file']
        
        if file.filename == '' or not allowed_file(file.filename):
            return jsonify({
                'success': False,
                'error': 'Invalid file'
            }), 400
        
        filename = safe_filename_with_ext(file)
        upload_folder = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'uploads')
        os.makedirs(upload_folder, exist_ok=True)
        filepath = os.path.join(upload_folder, filename)
        file.save(filepath)
        
        importer = DataImporter()
        success = importer.import_courses(filepath)
        summary = importer.get_summary()
        
        try:
            os.remove(filepath)
        except:
            pass
        
        return jsonify({
            'success': success,
            'data': summary
        }), 200 if success else 400
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@api_bp.route('/import/grades', methods=['POST'])
@jwt_required()
def import_grades():
    """导入成绩数据"""
    try:
        current_user_id = int(get_jwt_identity())
        user = User.query.get(current_user_id)
        
        if user.role != 'admin':
            return jsonify({
                'success': False,
                'error': 'Permission denied'
            }), 403
        
        if 'file' not in request.files:
            return jsonify({
                'success': False,
                'error': 'No file provided'
            }), 400
        
        file = request.files['file']
        
        if file.filename == '' or not allowed_file(file.filename):
            return jsonify({
                'success': False,
                'error': 'Invalid file'
            }), 400
        
        filename = safe_filename_with_ext(file)
        upload_folder = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'uploads')
        os.makedirs(upload_folder, exist_ok=True)
        filepath = os.path.join(upload_folder, filename)
        file.save(filepath)
        
        importer = DataImporter()
        success = importer.import_grades(filepath)
        summary = importer.get_summary()
        
        try:
            os.remove(filepath)
        except:
            pass
        
        return jsonify({
            'success': success,
            'data': summary
        }), 200 if success else 400
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@api_bp.route('/import/student_grades', methods=['POST'])
@jwt_required()
def import_student_grades():
    """合并导入学生与成绩数据"""
    try:
        current_user_id = int(get_jwt_identity())
        user = User.query.get(current_user_id)
        
        if user.role != 'admin':
            return jsonify({'success': False, 'error': 'Permission denied'}), 403
        
        if 'file' not in request.files:
            return jsonify({'success': False, 'error': 'No file provided'}), 400
        
        file = request.files['file']
        if file.filename == '' or not allowed_file(file.filename):
            return jsonify({'success': False, 'error': 'Invalid file'}), 400
        
        filename = safe_filename_with_ext(file)
        upload_folder = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'uploads')
        os.makedirs(upload_folder, exist_ok=True)
        filepath = os.path.join(upload_folder, filename)
        file.save(filepath)
        
        importer = DataImporter()
        success = importer.import_student_grades(filepath)
        summary = importer.get_summary()
        
        try:
            os.remove(filepath)
        except:
            pass
        
        return jsonify({'success': success, 'data': summary}), 200 if success else 400
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500


# 批量导入配置
BATCH_IMPORT_CONFIG = {
    'max_files_per_batch': 10,
    'max_total_size_mb': 100,
    'concurrent_processing': 3
}


@api_bp.route('/import/batch/<data_type>', methods=['POST'])
@jwt_required()
def batch_import_files(data_type):
    """批量导入多个文件"""
    try:
        current_user_id = int(get_jwt_identity())
        user = User.query.get(current_user_id)
        
        # 只有管理员可以导入数据
        if user.role != 'admin':
            return jsonify({
                'success': False,
                'error': 'Permission denied'
            }), 403
        
        # 验证数据类型
        valid_types = ['students', 'teachers', 'courses', 'student_grades']
        if data_type not in valid_types:
            return jsonify({
                'success': False,
                'error': f'Invalid data type. Must be one of: {valid_types}'
            }), 400
        
        # 获取上传的文件列表
        files = request.files.getlist('files[]')
        
        # 调试日志
        current_app.logger.info(f"Batch import request for {data_type}")
        current_app.logger.info(f"Request files keys: {list(request.files.keys())}")
        current_app.logger.info(f"Files received: {len(files)}")
        
        # 详细检查每个文件对象
        for i, file in enumerate(files):
            current_app.logger.info(f"File {i} - Name: {file.filename}")
            current_app.logger.info(f"File {i} - Content Type: {file.content_type}")
            current_app.logger.info(f"File {i} - Content Length: {file.content_length}")
            current_app.logger.info(f"File {i} - Headers: {dict(file.headers)}")
            
            # 尝试读取文件内容（然后重置指针）
            try:
                file.seek(0)
                content_sample = file.read(100)  # 读取前100字节
                file.seek(0)  # 重置文件指针
                current_app.logger.info(f"File {i} - Content sample: {content_sample[:50]}...")
                current_app.logger.info(f"File {i} - Actual content length: {len(content_sample)} bytes")
            except Exception as read_err:
                current_app.logger.error(f"File {i} - Error reading content: {read_err}")
        
        if not files or len(files) == 0:
            current_app.logger.error("No files provided in batch import request")
            return jsonify({
                'success': False,
                'error': 'No files provided'
            }), 400
        
        # 检查文件数量限制
        if len(files) > BATCH_IMPORT_CONFIG['max_files_per_batch']:
            return jsonify({
                'success': False,
                'error': f'Too many files. Maximum {BATCH_IMPORT_CONFIG["max_files_per_batch"]} files allowed'
            }), 400
        
        # 检查文件大小总和
        total_size = sum(file.content_length or 0 for file in files if hasattr(file, 'content_length'))
        max_size_bytes = BATCH_IMPORT_CONFIG['max_total_size_mb'] * 1024 * 1024
        
        # 调试日志
        current_app.logger.info(f"Total file size: {total_size} bytes, Max allowed: {max_size_bytes} bytes")
        
        if total_size > max_size_bytes:
            current_app.logger.error(f"File size limit exceeded: {total_size} > {max_size_bytes}")
            return jsonify({
                'success': False,
                'error': f'Total file size exceeds {BATCH_IMPORT_CONFIG["max_total_size_mb"]}MB limit'
            }), 400
        
        # 验证所有文件
        valid_files = []
        for file in files:
            current_app.logger.info(f"Processing file: {file.filename}")
            if file.filename == '':
                current_app.logger.warning("Empty filename detected, skipping")
                continue
                
            if not allowed_file(file.filename):
                current_app.logger.error(f"File type not allowed: {file.filename}")
                return jsonify({
                    'success': False,
                    'error': f'Invalid file type for {file.filename}. Only CSV and Excel files are allowed.'
                }), 400
            
            valid_files.append(file)
            
        current_app.logger.info(f"Valid files count: {len(valid_files)}")
        
        if not valid_files:
            return jsonify({
                'success': False,
                'error': 'No valid files found'
            }), 400
        
        # 创建上传目录
        upload_folder = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'uploads', 'batch')
        os.makedirs(upload_folder, exist_ok=True)
        
        current_app.logger.info(f"Upload folder: {upload_folder}")
        current_app.logger.info(f"Starting batch processing for {len(valid_files)} files")
        
        # 保存文件并并行处理
        results = process_batch_import(valid_files, data_type, upload_folder)
        
        current_app.logger.info(f"Batch processing completed. Results: {len(results)} files processed")
        
        # 统计结果
        total_files = len(results)
        success_files = sum(1 for r in results if r['success'])
        fail_files = total_files - success_files
        
        total_success_records = sum(r.get('data', {}).get('success_count', 0) for r in results if r['success'])
        total_fail_records = sum(r.get('data', {}).get('fail_count', 0) for r in results)
        
        current_app.logger.info(f"=== Batch Import Results Summary ===")
        current_app.logger.info(f"Total files: {total_files}")
        current_app.logger.info(f"Success files: {success_files}")
        current_app.logger.info(f"Fail files: {fail_files}")
        current_app.logger.info(f"Results details: {results}")
        current_app.logger.info(f"Total success records: {total_success_records}")
        current_app.logger.info(f"Total fail records: {total_fail_records}")
        
        status_code = 200 if success_files > 0 else 400
        current_app.logger.info(f"Returning HTTP status: {status_code}")
        
        return jsonify({
            'success': success_files > 0,
            'data': {
                'batch_summary': {
                    'total_files': total_files,
                    'success_files': success_files,
                    'fail_files': fail_files,
                    'total_success_records': total_success_records,
                    'total_fail_records': total_fail_records
                },
                'file_results': results
            }
        }), status_code
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


def process_batch_import(files, data_type, upload_folder):
    """同步处理批量导入（避免应用上下文问题）"""
    results = []
    
    # 同步处理每个文件，避免线程上下文问题
    for file in files:
        try:
            current_app.logger.info(f"Processing file: {file.filename}")
            result = process_single_import_file(file, data_type, upload_folder)
            result['filename'] = file.filename
            results.append(result)
            current_app.logger.info(f"File {file.filename} processed successfully: {result}")
        except Exception as e:
            current_app.logger.error(f"Error processing file {file.filename}: {str(e)}", exc_info=True)
            results.append({
                'success': False,
                'filename': file.filename,
                'error': str(e)
            })
    
    return results


def process_single_import_file(file, data_type, upload_folder):
    """处理单个文件的导入"""
    try:
        current_app.logger.info(f"=== Processing single file: {file.filename} ===")
        current_app.logger.info(f"File object type: {type(file)}")
        current_app.logger.info(f"File content length: {getattr(file, 'content_length', 'Unknown')}")
        
        # 保存文件（保留/补全扩展名，已带时间戳）
        filename = safe_filename_with_ext(file)
        import os
        filepath = os.path.join(upload_folder, filename)
        
        current_app.logger.info(f"Saving file to: {filepath}")
        file.save(filepath)
        
        # 检查保存后的文件大小
        if os.path.exists(filepath):
            file_size = os.path.getsize(filepath)
            current_app.logger.info(f"Saved file size: {file_size} bytes")
        else:
            current_app.logger.error(f"File not saved: {filepath}")
            return {'success': False, 'error': 'File not saved'}
        
        # 导入数据
        current_app.logger.info(f"Starting data import for type: {data_type}")
        importer = DataImporter()
        
        if data_type == 'students':
            success = importer.import_students(filepath)
        elif data_type == 'teachers':
            success = importer.import_teachers(filepath)
        elif data_type == 'courses':
            success = importer.import_courses(filepath)
        elif data_type == 'student_grades':
            current_app.logger.info("Calling importer.import_student_grades")
            success = importer.import_student_grades(filepath)
            current_app.logger.info(f"Import result: {success}")
        else:
            raise ValueError(f'Unsupported data type: {data_type}')
        
        summary = importer.get_summary()
        current_app.logger.info(f"Import summary: {summary}")
        
        # 删除临时文件
        try:
            os.remove(filepath)
            current_app.logger.info(f"Temporary file deleted: {filepath}")
        except Exception as delete_err:
            current_app.logger.warning(f"Failed to delete temp file: {delete_err}")
        
        result = {
            'success': success,
            'data': summary
        }
        current_app.logger.info(f"Function result: {result}")
        return result
        
    except Exception as e:
        current_app.logger.error(f"Exception in process_single_import_file: {str(e)}", exc_info=True)
        # 尝试删除临时文件
        try:
            if 'filepath' in locals():
                os.remove(filepath)
        except:
            pass
        
        raise e


@api_bp.route('/import/batch/config', methods=['GET'])
@jwt_required()
def get_batch_import_config():
    """获取批量导入配置"""
    try:
        current_user_id = int(get_jwt_identity())
        user = User.query.get(current_user_id)
        
        if user.role != 'admin':
            return jsonify({
                'success': False,
                'error': 'Permission denied'
            }), 403
        
        return jsonify({
            'success': True,
            'data': BATCH_IMPORT_CONFIG
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500


@api_bp.route('/import/templates/<data_type>', methods=['GET'])
@jwt_required()
def download_template(data_type):
    """下载导入模板"""
    try:
        templates = {
            'students': '学号,邮箱,密码,名,姓,手机号,学院,系部,专业,年级,入学日期\n2021001001,student1001@university.edu.cn,password123,三,张,13800138000,计算机学院,计算机科学与技术系,计算机科学与技术,2,2021-09-01',
            'teachers': '工号,邮箱,密码,名,姓,手机号,学院,系部,职称\nT0031,teacher31@university.edu.cn,password123,四,李,13900139000,计算机学院,软件工程系,教授',
            'courses': '课程代码,课程名称,课程描述,学分,院系,授课教师ID,学期(旧),学年,学期,课程类别,教学班号,最大人数,标签\n120907,计算机网络,专业核心课程,3.5,计算机学院,T0001,,2023-2024,1,专业核心课,01,100,"网络,算法"',
            'grades': '学号,课程代码,选课日期,学年,学期,考试类型,学分类型,绩点,状态,总评成绩,平时成绩,考试成绩,总课时,出勤课时,出勤率,教学班号\n2021000001,120907,2023-09-01,2023-2024,1,正常考试,主修,3.10,completed,82,80,85,48,45,93.75,01',
            'student_grades': '学号,用户名,邮箱,密码,名,姓,手机号,学院,系部,专业,年级,入学日期,课程代码,选课日期,学年,学期,考试类型,学分类型,绩点,状态,总评成绩,平时成绩,考试成绩,总课时,出勤课时,出勤率,教学班号\n2021000001,2021000001,student@example.com,password123,三,张,13800138000,计算机学院,软件工程系,软件工程,3,2021-09-01,120907,2023-09-01,2023-2024,1,正常考试,主修,3.10,completed,82,80,85,48,45,93.75,01'
        }
        
        if data_type not in templates:
            return jsonify({
                'success': False,
                'error': 'Invalid template type'
            }), 400
        
        return jsonify({
            'success': True,
            'data': {
                'filename': f'{data_type}_template.csv',
                'content': templates[data_type]
            }
        }), 200
        
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500










